﻿Imports System.Data.OleDb

Module bd

    Public cn As New OleDbConnection
    Dim command As OleDbCommand
    Dim miDataSet As New DataSet
    Dim adaptador As New OleDb.OleDbDataAdapter
    Dim reader As OleDbDataReader
    Public sConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" &
                    Application.StartupPath & "\database\database.accdb;Persist Security Info=False;"

    Public id As String
    Public perfil As String
    Public NombreEmpleado As String
    Public ApellidoEmpleado As String

    Public permFacturacion As Boolean
    Public permCompras As Boolean
    Public permReportes As Boolean
    Public permCatProducto As Boolean
    Public permInventario As Boolean
    Public permUsuario As Boolean
    Public permRespaldo As Boolean
    Public permProveedor As Boolean

    'Abre Conexion con Base de Datos
    Public Sub openConnection()
        Try
            cn = New OleDbConnection(sConexion)
            cn.Open()
        Catch
            MsgBox("Error: " & Err.Description & vbCrLf & "Número: " & Err.Number)
            End
        End Try

    End Sub

    'Cierra Conexion con BaseDatos
    Public Sub closeConnection()
        If (cn.State And ConnectionState.Open) <> 0 Then
            cn.Close()
        End If
    End Sub

    'Ejecuta Query de Consulta
    Function validaIngreso(ByVal user As String, ByVal pass As String) As Array
        Dim valida(3) As String
        Dim recordCount As Integer = 0
        Dim id As String = vbNull
        Dim perfil As String = vbNull
        Dim estatus As String = vbNull

        'Se crea la Consulta
        Dim sql As String = "SELECT U.Cedula, U.Usuario, U.Clave, E.Estatus, E.ID_Perfil, ModFacturacion, ModCompras, ModReportes, ModProveedor, ModCatProducto, ModInventario, ModUsuarios, ModRespaldo " _
                            & "FROM usuario as U, empleado as E Where U.Cedula = E.Cedula and Usuario = '" & user & "' and Clave = '" & pass & "'"

        command = New OleDbCommand(sql, cn)

        reader = command.ExecuteReader
        Do While reader.Read()

            id = reader.Item("Cedula")
            Dim ur As String = reader.Item("Usuario")
            Dim cl As String = reader.Item("Clave")
            estatus = reader.Item("Estatus")
            perfil = reader.Item("ID_Perfil")

            permFacturacion = reader.Item("ModFacturacion")
            permCompras = reader.Item("ModCompras")
            permReportes = reader.Item("ModReportes")
            permProveedor = reader.Item("ModProveedor")
            permCatProducto = reader.Item("ModCatProducto")
            permInventario = reader.Item("ModInventario")
            permUsuario = reader.Item("ModUsuarios")
            permRespaldo = reader.Item("ModRespaldo")

            recordCount += 1
        Loop

        reader.Close()

        If recordCount = 1 Then
            valida(0) = True
            valida(1) = id
            valida(2) = estatus
            valida(3) = perfil
        Else
            valida(0) = False
        End If

        Return valida
    End Function

    'Query Consulta Usuarios
    Sub queryConsultaEmpleado(ByVal search As String)

        Dim sql As String = ("SELECT E.Cedula, E.Nombre,E.Ape1, E.Ape2, E.Estado_Civil, IIF(E.Estatus = '0', 'Inactivo','Activo') as Estado," +
        " P.Descripcion, E.Direccion, E.Fecha_Nacimiento, U.Usuario, U.Clave, P.ID_Perfil, E.Estatus, E.Foto, U.ModFacturacion, U.ModCompras, U.ModReportes, U.ModProveedor, U.ModCatProducto, U.Modinventario, U.ModUsuarios, U.ModRespaldo" +
        " FROM empleado as E, usuario as U, perfil as P WHERE E.Cedula = U.Cedula and E.ID_Perfil = P.ID_Perfil" +
        " and (E.Cedula LIKE @SEARCH or E.Nombre LIKE @SEARCH or E.Ape1 LIKE @SEARCH or E.Ape2 LIKE @SEARCH)")

        adaptador = New OleDbDataAdapter(sql, cn)
        miDataSet = New DataSet

        adaptador.SelectCommand.Parameters.Add(New OleDbParameter("@SEARCH", OleDbType.VarWChar, 20))

        adaptador.SelectCommand.Parameters("@SEARCH").Value = "%" & search & "%"

        adaptador.Fill(miDataSet, "empleado")

        frmAdminUser.dgvUser.DataSource = miDataSet
        frmAdminUser.dgvUser.DataMember = "empleado"

    End Sub

    'Query Consulta Usuarios
    Sub queryComboBoxPerfil()

        Dim adaptadorComboBox As OleDbDataAdapter
        Dim dataSetComboBox As DataSet

        Dim sql As String = ("SELECT ID_Perfil, Descripcion FROM perfil")

        adaptadorComboBox = New OleDbDataAdapter(sql, cn)
        dataSetComboBox = New DataSet

        adaptadorComboBox.Fill(dataSetComboBox, "perfil")

        frmAdminUser.cmbPerfil.DataSource = dataSetComboBox
        frmAdminUser.cmbPerfil.DisplayMember = "perfil.Descripcion"
        frmAdminUser.cmbPerfil.ValueMember = "perfil.ID_Perfil"


    End Sub

    Sub getCategoria(ByVal search As String, ByVal path As String, ByVal tipo As String)
        cn = New OleDbConnection(sConexion)

        adaptador = New OleDbDataAdapter("SELECT IdCategoria, Descripcion FROM categoriaProducto WHERE IdCategoria LIKE @SEARCH or Descripcion LIKE @SEARCH", cn)
        miDataSet = New DataSet

        adaptador.SelectCommand.Parameters.Add(New OleDbParameter("@SEARCH", OleDbType.VarWChar, 20))

        adaptador.SelectCommand.Parameters("@SEARCH").Value = "%" & search & "%"

        adaptador.Fill(miDataSet, "Categoria")

        If tipo = "grid" Then
            If path = "frmInventario" Then
                frmInventario.dgvCategoria.DataSource = miDataSet
                frmInventario.dgvCategoria.DataMember = "Categoria"
            ElseIf path = "frmCategoria" Then
                frmCategoria.CategoriaDataGridView.DataSource = miDataSet
                frmCategoria.CategoriaDataGridView.DataMember = "Categoria"
            End If

        ElseIf tipo = "comboBox" Then
            'frmCategoria.DescripcionComboBox.DataSource = CatDataSet
            'frmCategoria.DescripcionComboBox.DisplayMember = "Categoria.Descripcion"
            'frmCategoria.DescripcionComboBox.ValueMember = "Categoria.IdCategoria"
        End If

    End Sub

    Sub Inventario(ByVal tipoBusqueda As String, ByVal search As String, ByVal form As String)
        cn = New OleDbConnection(sConexion)

        If tipoBusqueda = "Codigo" Then
            adaptador = New OleDbDataAdapter("SELECT * FROM Inventario WHERE idCodigoProducto LIKE @SEARCH", cn)
        ElseIf tipoBusqueda = "productoEspecifico" Then
            adaptador = New OleDbDataAdapter("SELECT * FROM Inventario WHERE idCodigoProducto = @SEARCH", cn)
        ElseIf tipoBusqueda = "Producto" Then
            adaptador = New OleDbDataAdapter("SELECT * FROM Inventario WHERE NombreProducto LIKE @SEARCH", cn)
        Else
            adaptador = New OleDbDataAdapter("SELECT * FROM Inventario", cn)
        End If

        miDataSet = New DataSet

        adaptador.SelectCommand.Parameters.Add(New OleDbParameter("@SEARCH", OleDbType.VarWChar, 20))

        If (tipoBusqueda = "productoEspecifico") Then
            adaptador.SelectCommand.Parameters("@SEARCH").Value = search
        Else
            adaptador.SelectCommand.Parameters("@SEARCH").Value = "%" & search & "%"
        End If


        adaptador.Fill(miDataSet, "Inventario")


        If form = "frmInventario" Then
            frmInventario.dgvInventario.DataSource = miDataSet
            frmInventario.dgvInventario.DataMember = "Inventario"
        ElseIf form = "frmMuestraProductos" Then
            frmMuestraProductos.dgvInventario.DataSource = miDataSet
            frmMuestraProductos.dgvInventario.DataMember = "Inventario"
        ElseIf form = "ingresaProductoFactura" Then

            If miDataSet.Tables("Inventario").Rows.Count() = 0 Then
                MsgBox("No Existe Producto")
            Else
                Dim cantidad As Integer = frmFacturacion.txtCantidad.Text
                Dim totalProducto As Integer = cantidad * miDataSet.Tables(0).Rows(0).Item(10)

                If miDataSet.Tables(0).Rows(0).Item(7) < 0 Then
                    MsgBox("Cantidad del producto segun Inventario: " & miDataSet.Tables(0).Rows(0).Item(7))
                End If

                frmFacturacion.dgvFacturacion.Rows.Add(miDataSet.Tables(0).Rows(0).Item(0), miDataSet.Tables(0).Rows(0).Item(1), miDataSet.Tables(0).Rows(0).Item(10), cantidad, totalProducto)
                frmFacturacion.subTotal += totalProducto
                frmFacturacion.txtSubTotal.Text = frmFacturacion.subTotal

            End If

        End If
    End Sub

    Sub Proveedor(ByVal search As String, ByVal path As String)
        cn = New OleDbConnection(sConexion)

        adaptador = New OleDbDataAdapter("SELECT * FROM Proveedor WHERE IdProveedor LIKE @SEARCH or Nombre LIKE @SEARCH", cn)
        miDataSet = New DataSet

        adaptador.SelectCommand.Parameters.Add(New OleDbParameter("@SEARCH", OleDbType.VarWChar, 20))

        adaptador.SelectCommand.Parameters("@SEARCH").Value = "%" & search & "%"

        adaptador.Fill(miDataSet, "Proveedor")

        If path = "frmInventario" Then
            frmInventario.dgvProveedor.DataSource = miDataSet
            frmInventario.dgvProveedor.DataMember = "Proveedor"
        End If

    End Sub

    Sub setUsuario(ByVal idLogin As String, ByVal perfilLogin As String)
        'Se crea la Consulta
        Dim readerEmpleado As OleDbDataReader
        Dim sql As String = "SELECT E.Nombre, E.Ape1 FROM empleado as E Where E.Cedula = '" & idLogin & "'"

        command = New OleDbCommand(sql, cn)

        readerEmpleado = command.ExecuteReader
        Do While readerEmpleado.Read()
            NombreEmpleado = readerEmpleado.Item("Nombre")
            ApellidoEmpleado = readerEmpleado.Item("Ape1")
        Loop
        readerEmpleado.Close()

        id = idLogin
        perfil = perfilLogin
    End Sub

End Module
